package poliakov.spring.advertisement.persistence;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import poliakov.spring.advertisement.AdvertisementService;
import poliakov.spring.advertisement.pojo.IAdvertisement;
import poliakov.spring.advertisement.pojo.ICategory;
import poliakov.spring.advertisement.pojo.ICity;
import poliakov.spring.advertisement.pojo.PPAadvertisement;
import poliakov.spring.advertisement.pojo.PPCategory;
import poliakov.spring.advertisement.pojo.PPCity;

import java.util.Date;
import java.util.List;

@SuppressWarnings("deprecation")
public class AdvertisementJdbcDao extends SimpleJdbcDaoSupport implements
AdvertisementDao {

	public int generateID() {
	    return getSimpleJdbcTemplate().queryForObject(
	            "select Adv_sq.nextval from dual",
	        new ParameterizedRowMapper<Integer>() {
	          public Integer mapRow(ResultSet rs, int rowNum) 
	              throws SQLException {
	        	Integer id = rs.getInt(1);
	            return id;
	          }
	        });
	}
	
	@Override
	public IAdvertisement getAadvertisementByID(int id) {
	    return getSimpleJdbcTemplate().queryForObject(
	            "select * from pp_advertisement where adv_id = ?",
	        new ParameterizedRowMapper<IAdvertisement>() {
	          public IAdvertisement mapRow(ResultSet rs, int rowNum) 
	              throws SQLException {
	        	  return generateAdvertisement(rs);
	          }
	        }, id);
	}

	@Override
	public void addAdvertisement(IAdvertisement advertisement) {
		int id=generateID();
	    getSimpleJdbcTemplate().update(
	    "INSERT INTO pp_advertisement VALUES (?,?,?,?,?)",
	            new Object[] { id, advertisement.getCategoryID(), advertisement.getCityID(),
	    		advertisement.getPublicationDate(), advertisement.getText()});
	    advertisement.setAdvID(id);
	}

	@Override
	public ICity getCityByID(int id) {
	    return getSimpleJdbcTemplate().queryForObject(
	            "select * from pp_cities where city_id = ?",
	        new ParameterizedRowMapper<ICity>() {
	          public ICity mapRow(ResultSet rs, int rowNum) 
	              throws SQLException {
	        	  return generateCity(rs);
	          }
	        }, id);
		//return null;
	}

	@Override
	public void addCity(ICity city) {
		// TODO Auto-generated method stub

	}

	@Override
	public ICategory getCategoryByID(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void addCategory(ICategory category) {
		// TODO Auto-generated method stub

	}

	@Override
	public List<IAdvertisement> getAllAadvertisements() {
		 return getSimpleJdbcTemplate().query("select * from pp_advertisement",
				 new ParameterizedRowMapper<IAdvertisement>() {
					public IAdvertisement mapRow(ResultSet rs, int rowNum) 
				      throws SQLException {
					  return generateAdvertisement(rs);
				  }
				});
	}
	
	@Override
	public List<ICity> getAllCityies() {
		 return getSimpleJdbcTemplate().query("select * from pp_cities",
				 new ParameterizedRowMapper<ICity>() {
					public ICity mapRow(ResultSet rs, int rowNum) 
				      throws SQLException {
					  return generateCity(rs);
				  }
				});
	}

	@Override
	public List<ICategory> getAllCategories() {
		 return getSimpleJdbcTemplate().query("select * from pp_categories",
				 new ParameterizedRowMapper<ICategory>() {
					public ICategory mapRow(ResultSet rs, int rowNum) 
				      throws SQLException {
					  return generateCategory(rs);
				  }
				});
	}		
	
	protected static IAdvertisement generateAdvertisement(ResultSet rs) throws SQLException{
    	IAdvertisement advertisement = new PPAadvertisement();
        advertisement.setAdvID(rs.getInt(1));
        advertisement.setCategoryID(rs.getInt(2));
        advertisement.setCityID(rs.getInt(3));
        advertisement.setPublicationDate(rs.getDate(4));
        advertisement.setText(rs.getString(5));
        return advertisement;
	}
	
	protected static ICity generateCity(ResultSet rs) throws SQLException{
    	ICity city = new PPCity();
    	city.setCityID(rs.getInt(1));
    	city.setCityName(rs.getString(2));
        return city;
	}
	
	protected static ICategory generateCategory(ResultSet rs) throws SQLException{
    	ICategory category = new PPCategory();
    	category.setCategoryID(rs.getInt(1));
    	category.setParentID(rs.getInt(2));    	
    	category.setCategoryName(rs.getString(3));
        return category;
	}	



}
